package com.example.personalfoodadmin.database;

import android.content.Context;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DatabaseHandler extends SQLiteOpenHelper
{
    public static final int DATABASE_VERSION = 1;

    public static final String DATABASE_NAME = "mydatabase.db";

    public static final String USERPROFILE_TABLE = "userprofile";

    public static final String USERPROFILE_ID = "_id";

    public static final String PRENAME = "prename";

    public static final String SURNAME = "surname";

    public static final String SEX = "sex";

    public static final String DATE = "date";

    public static final String BODYHEIGHT = "bodyheight";

    public static final String BODYWEIGHT = "bodyweight";

    public static final String BMI = "bmi";

    public static final String ACTIVITYTYPE = "activitytype";

    public static final String DIETTYPE = "diettype";

    public static final String CALORIES = "calories";

    // ********************************************************************************************

    public static int id_index = 2;

    public static final String FOOD_TABLE = "food";

    // Food identifier (primary key)
    public static final String FOOD_ID = "_id";

    // Name of food
    public static final String FOOD_NAME = "name";

    // Description of food
    public static final String FOOD_DESC = "description";

    public static final String FOOD_CALORIES = "calories";

    public static final String FOOD_FAT = "fat";

    public static final String FOOD_DIETTYPE = "diettype";

    // *********************************************************************************************************

    public static final String EK_TABLE = "ek";

    public static final String PROFILE_USER_ID_FOR_EK = "user_id";

    public static final String FOOD_NAME_FOR_EK = "food_name";

    public static final String EK_AMOUNT = "amount";

    // **********************************************************************************************************

    public static final String STOCK_TABLE = "stock";

    public static final String PROFILE_USER_ID_FOR_STOCK = "user_id";

    public static final String FOOD_NAME_FOR_STOCK = "food_name";

    public static final String STOCK_AMOUNT = "amount";

    // ***********************************************************************************************************

    public static final String PREF_TABLE = "pref";

    public static final String PROFILE_USER_ID_FOR_PREF = "user_id";

    public static final String FOOD_NAME_FOR_PREF = "food_name";

    public DatabaseHandler(Context context)
    {
        super(context, DATABASE_NAME, null, DATABASE_VERSION);
    }

    @Override
    public void onCreate(SQLiteDatabase db)
    {
        String sqlCreateTableStatement = "CREATE TABLE IF NOT EXISTS " + USERPROFILE_TABLE + " (" + USERPROFILE_ID + " INTEGER PRIMARY KEY AUTOINCREMENT, " + PRENAME + " TEXT NOT NULL, " + SURNAME + " TEXT NOT NULL, " + SEX + " TEXT NOT NULL, " + DATE + " TEXT NOT NULL, " + BODYHEIGHT + " INTEGER NOT NULL, " + BODYWEIGHT + " INTEGER NOT NULL, " + BMI + " INTEGER NOT NULL, " + ACTIVITYTYPE + " TEXT NOT NULL, " + DIETTYPE + " TEXT NOT NULL, " + CALORIES + " INTEGER NOT NULL);";

        String createFoodTable = "CREATE TABLE IF NOT EXISTS " + FOOD_TABLE + "(" + FOOD_ID + " INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL ," + FOOD_NAME + " TEXT NOT NULL, " + FOOD_DESC + " TEXT NOT NULL, " + FOOD_CALORIES + " INTEGER NOT NULL, " + FOOD_FAT + " INTEGER NOT NULL, " + FOOD_DIETTYPE + " TEXT NOT NULL);";

        String privateListCreateStatement = "CREATE TABLE IF NOT EXISTS " + EK_TABLE + " (" + PROFILE_USER_ID_FOR_EK + " INTEGER(8) NOT NULL, " + FOOD_NAME_FOR_EK + " TEXT NOT NULL, " + EK_AMOUNT + " INTEGER, FOREIGN KEY (" + PROFILE_USER_ID_FOR_EK + ") REFERENCES " + USERPROFILE_TABLE + " (" + USERPROFILE_ID + ")," + "FOREIGN KEY (" + FOOD_NAME_FOR_EK + ") REFERENCES " + FOOD_TABLE + " (" + FOOD_ID + "));";

        String stockListCreateStatement = "CREATE TABLE IF NOT EXISTS " + STOCK_TABLE + " (" + PROFILE_USER_ID_FOR_STOCK + " INTEGER(8) NOT NULL, " + FOOD_NAME_FOR_STOCK + " TEXT NOT NULL, " + STOCK_AMOUNT + " INTEGER, FOREIGN KEY (" + PROFILE_USER_ID_FOR_STOCK + ") REFERENCES " + USERPROFILE_TABLE + " (" + USERPROFILE_ID + ")," + "FOREIGN KEY (" + FOOD_NAME_FOR_STOCK + ") REFERENCES " + FOOD_TABLE + " (" + FOOD_ID + "));";

        String prefListCreateStatement = "CREATE TABLE IF NOT EXISTS " + PREF_TABLE + " (" + PROFILE_USER_ID_FOR_PREF + " INTEGER(8) NOT NULL, " + FOOD_NAME_FOR_PREF + " TEXT NOT NULL, FOREIGN KEY (" + PROFILE_USER_ID_FOR_STOCK + ") REFERENCES " + USERPROFILE_TABLE + " (" + USERPROFILE_ID + ")," + "FOREIGN KEY (" + FOOD_NAME_FOR_STOCK + ") REFERENCES " + FOOD_TABLE + " (" + FOOD_ID + "));";

        // create table
        db.execSQL(createFoodTable);

        Log.d("createFoodTable", "ok");

        db.execSQL(sqlCreateTableStatement);

        Log.d("createUserProfile", "ok");

        insertFood(db);

        db.execSQL(privateListCreateStatement);

        Log.d("createprivatelist", "ok");

        db.execSQL(stockListCreateStatement);

        Log.d("createstocklist", "ok");

        db.execSQL(prefListCreateStatement);

        Log.d("createpreflist", "ok");
    }

    @Override
    public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
    {

    }

    public void insertFood(SQLiteDatabase db)
    {
        String insertFoodIntoDB = "INSERT INTO " + FOOD_TABLE + "(" + FOOD_NAME + "," + FOOD_DESC + "," + FOOD_CALORIES + "," + FOOD_FAT + "," + FOOD_DIETTYPE + ") " + " values('RindFleisch', 'Leckeres Fleisch vom Rind', 300, 150, 'normal');";

        db.execSQL(insertFoodIntoDB);

        Log.d("insert food", "ok");
    }

}
